package com.qa.service.impl;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.stereotype.Service;

import com.qa.IDao.weekplanMapper;
import com.qa.pojo.Demand;
import com.qa.pojo.ZNode;
import com.qa.pojo.weekplan;
import com.qa.pojo.weekplanStatistics;
import com.qa.service.IWeekAndMonthService;

@SuppressWarnings("deprecation")
@Service("iWeekAndMonthService")
public class IWeekAndMonthServiceImpl implements IWeekAndMonthService {

	Logger logger = Logger.getLogger(this.getClass());
	@Resource
	private weekplanMapper weekPlanDao;

	@Override
	public int selectWeekNumIsExist(int weeknum, int usersid) {
		// TODO Auto-generated method stub
		return this.weekPlanDao.selectWeekNumIsExist(weeknum, usersid);
	}

	@Override
	public int insertWeekPlan(List<weekplan> weekplansList) {
		// TODO Auto-generated method stub
		return this.weekPlanDao.insertWeekPlan(weekplansList);
	}

	@Override
	public List<ZNode> selectDeptOrTeam(int usersid) {
		// TODO Auto-generated method stub
		return this.weekPlanDao.selectDeptOrTeam(usersid);
	}

	@Override
	public List<weekplanStatistics> selectWeekPlan(int isSelf, int usersid,
			int isDeptOrTeam, int beginweeknum, int endweeknum,
			List<Integer> trueGroup, int selectPerson, int beginYear,
			int endYear) {
		// TODO Auto-generated method stub
		return this.weekPlanDao.selectWeekPlanStatistics(isSelf, usersid,
				isDeptOrTeam, beginweeknum, endweeknum, trueGroup,
				selectPerson, beginYear, endYear);
	}

	@Override
	public List<weekplan> getEditWeekPlan(int weeknum, int publish,int thisYear) {
		// TODO Auto-generated method stub
		return this.weekPlanDao.getEditWeekPlan(weeknum, publish,thisYear);
	}

	@Override
	public int deleteExistWeek(int weeknum, int currenteditUsersid) {
		// TODO Auto-generated method stub
		return this.weekPlanDao.deleteExistWeek(weeknum, currenteditUsersid);
	}

	@Override
	public void exportData(List<weekplan> weekplansList,
			weekplanStatistics weekplanSta, Double selfperSum,
			Double bossperSum, String templatePath, HttpServletResponse response) {
		// 获取文件输入流，创建工作簿
		FileInputStream fis;
		try {
			fis = new FileInputStream(new File(templatePath));
			HSSFWorkbook workBook = new HSSFWorkbook(fis);

			String deptnameString = "";
			if (weekplanSta.getDeptname() == null
					|| weekplanSta.getDeptname().equals("")
					|| weekplanSta.getDeptname().equals("null")) {
				if (weekplanSta.getProjectteam() != null
						&& !weekplanSta.getProjectteam().equals("null")
						&& !weekplanSta.getProjectteam().equals("")) {
					deptnameString = weekplanSta.getProjectteam() + "-";
				}
			} else {
				deptnameString = weekplanSta.getDeptname() + "-";
			}
			// 设置导出文件名，并编码
			String fileName = deptnameString + weekplanSta.getPublish() + "-"
					+ weekplanSta.getWeeknum() + "-周计划总结" + ".xls";

			response.reset();
			response.setContentType("application/octet-stream;charset=UTF-8");
			try {
				response.setHeader(
						"Content-Disposition",
						"attachment;Filename="
								+ new String(fileName.getBytes("GBK"),
										"ISO-8859-1"));
			} catch (Exception e) {
				e.printStackTrace();
				this.logger.error("导出统计报表信息出错！请联系管理员" + e.getMessage());
			}
			/*
			 * response.setHeader("Content-Disposition",
			 * "attachment; filename=\"" + fileName + "\"");
			 * response.setContentType
			 * ("application/octet-stream;charset=UTF-8");
			 */

			// 创建输出流
			OutputStream outputStream = new BufferedOutputStream(
					response.getOutputStream());
			exportData(workBook, weekplansList, weekplanSta, selfperSum,
					bossperSum, 1);
			// 删除模板页
			workBook.removeSheetAt(0);
			// 将工作簿写入输出流
			workBook.write(outputStream);
			// 关闭资源流
			fis.close();
			outputStream.flush();
			outputStream.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.info(e.getMessage());
		}
	}

	/**
	 * 
	 * exportUserData:将数据写入工作簿. <br/>
	 * 
	 * @author qiyongkang
	 * @param workBook
	 * @param userList
	 * @since JDK 1.6
	 */
	private void exportData(HSSFWorkbook workBook,
			List<weekplan> weekplansList, weekplanStatistics weekplanSta,
			Double selfperSum, Double bossperSum, int sheetNum) {
		// 克隆模板sheet
		HSSFSheet singleSheet = workBook.cloneSheet(0);

		singleSheet.setActive(true);
		// 设置克隆的sheet名称
		workBook.setSheetName(sheetNum, weekplanSta.getWeeknum() + "周-"
				+ weekplanSta.getPublish());
		// 获取cellStyle
		// 定义起始行，从第8行开始
		int singleRowIndex = 7;
		// 格式化時間
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(new Date());
		SimpleDateFormat myFmt = new SimpleDateFormat("yyyy-MM-dd");
		HSSFCellStyle style = workBook.createCellStyle();
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		style.setWrapText(true);// 设置自动换行
		HSSFCellStyle style1 = workBook.createCellStyle();
		HSSFFont font = workBook.createFont();
		font.setColor(HSSFColor.RED.index);
		style1.setFont(font);
		style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style1.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		style1.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		style1.setWrapText(true);// 设置自动换行
		String lastObjectString = "";
		String lastsource = "";
		// 准备下拉列表数据
		String[] strEfficiencys = new String[] { "提前完成", "按时完成", "滞后", "未启动" };
		String[] strEffects = new String[] { "超出预期", "符合预期", "效果一般", "效果很差" };
		// 设置第一列的1-10行为下拉列表
		CellRangeAddressList regions = null;
		// 创建下拉列表数据
		DVConstraint constraint = null;
		// 绑定
		HSSFDataValidation dataValidation = null;
		// 开始遍历
		for (weekplan wek : weekplansList) {
			try {
				// 起始列索引
				int singleColIndex = 0;
				// 创建行
				HSSFRow singleRow = singleSheet.createRow(singleRowIndex++);
				// 创建列
				HSSFCell singleCell = null;
				// 周工作目标,0
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(wek.getObject());
				singleCell.setCellStyle(style);
				if (singleRowIndex == 8
						|| singleRowIndex == weekplansList.size()) {
					// 首行不做处理
				} else {
					// 如果为空，或者内容和上一次一样，就合并
					if (wek.getObject() == null
							|| wek.getObject().trim().equals("")
							|| wek.getObject().trim().equals(lastObjectString)) {
						singleSheet.addMergedRegion(new CellRangeAddress(
								singleRowIndex - 2, singleRowIndex - 1,
								singleColIndex - 1, singleColIndex - 1));
					}
				}
				lastObjectString = wek.getObject() == null ? "" : wek
						.getObject().trim();
				// 目标来源,1
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(wek.getSourceid());
				singleCell.setCellStyle(style);
				if (singleRowIndex == 8
						|| singleRowIndex == weekplansList.size()) {
					// 首行不做处理
				} else {
					// 如果为空，或者内容和上一次一样，就合并
					if (wek.getSourceid() == null
							|| wek.getSourceid().trim().equals("")
							|| wek.getSourceid().trim().equals(lastsource)) {
						singleSheet.addMergedRegion(new CellRangeAddress(
								singleRowIndex - 2, singleRowIndex - 1,
								singleColIndex - 1, singleColIndex - 1));
					}
				}
				lastsource = wek.getSourceid() == null ? "" : wek.getSourceid()
						.trim();
				// 关键结果分析,2
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(wek.getKeyresults());
				singleCell.setCellStyle(style);
				// 协助人
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(wek.getNeedperson());
				singleCell.setCellStyle(style);
				// 重要性权重
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(wek.getKeyresultsper() == null ? "0"
						: wek.getKeyresultsper() + "%");
				singleCell.setCellStyle(style);
				// 完成标准
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(wek.getKeyresultstandard());
				singleCell.setCellStyle(style);
				// 开始日期
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(myFmt.format(wek.getBegintime()));
				singleCell.setCellStyle(style);
				// 完成日期
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(myFmt.format(wek.getEndtime()));
				singleCell.setCellStyle(style);
				// 效率自评
				singleCell = singleRow.createCell(singleColIndex++);
				// 设置第一列的1-10行为下拉列表
				regions = new CellRangeAddressList(singleRowIndex - 1,
						singleRowIndex - 1, singleColIndex - 1,
						singleColIndex - 1);
				// 创建下拉列表数据
				constraint = DVConstraint
						.createExplicitListConstraint(strEfficiencys);
				// 绑定
				dataValidation = new HSSFDataValidation(regions, constraint);
				singleSheet.addValidationData(dataValidation);
				singleCell.setCellValue(wek.getEfficiency());
				singleCell.setCellStyle(style);
				// 效果自评
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(wek.getEffect());
				// 设置第一列的1-10行为下拉列表
				regions = new CellRangeAddressList(singleRowIndex - 1,
						singleRowIndex - 1, singleColIndex - 1,
						singleColIndex - 1);
				// 创建下拉列表数据
				constraint = DVConstraint
						.createExplicitListConstraint(strEffects);
				// 绑定
				dataValidation = new HSSFDataValidation(regions, constraint);
				singleSheet.addValidationData(dataValidation);
				singleCell.setCellStyle(style);
				// 自评分
				singleCell = singleRow.createCell(singleColIndex++);

				singleCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
				String selfpergsString = "IF(I" + (singleRowIndex) + "&J"
						+ (singleRowIndex) + "=\"提前完成超出预期\",130,IF(OR(I"
						+ (singleRowIndex) + "&J" + (singleRowIndex)
						+ "=\"按时完成超出预期\",I" + (singleRowIndex) + "&J"
						+ (singleRowIndex) + "=\"提前完成符合预期\"),110,IF(OR(I"
						+ (singleRowIndex) + "&J" + (singleRowIndex)
						+ "=\"按时完成符合预期\",I" + (singleRowIndex) + "&J"
						+ (singleRowIndex) + "=\"提前完成效果一般\"),100,IF(OR(I"
						+ (singleRowIndex) + "&J" + (singleRowIndex)
						+ "=\"滞后超出预期\",I" + (singleRowIndex) + "&J"
						+ (singleRowIndex) + "=\"按时完成效果一般\"),90,IF(I"
						+ (singleRowIndex) + "&J" + (singleRowIndex)
						+ "=\"滞后符合预期\",80,IF(I" + (singleRowIndex) + "&J"
						+ (singleRowIndex) + "=\"滞后效果一般\",50))))))*E"
						+ (singleRowIndex) + "+IF(OR(I" + (singleRowIndex)
						+ "=\"未启动\",J" + (singleRowIndex) + "=\"效果很差\"),0)*E"
						+ (singleRowIndex);
				singleCell.setCellFormula(selfpergsString);
				singleCell.setCellValue(wek.getSelfper() == null ? 0 : wek
						.getSelfper());
				singleCell.setCellStyle(style);
				// 结果说明
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(wek.getResultremark());
				singleCell.setCellStyle(style1);
				// 效率总评
				singleCell = singleRow.createCell(singleColIndex++);
				// 设置第一列的1-10行为下拉列表
				regions = new CellRangeAddressList(singleRowIndex - 1,
						singleRowIndex - 1, singleColIndex - 1,
						singleColIndex - 1);
				// 创建下拉列表数据
				constraint = DVConstraint
						.createExplicitListConstraint(strEfficiencys);
				// 绑定
				dataValidation = new HSSFDataValidation(regions, constraint);
				singleSheet.addValidationData(dataValidation);
				singleCell.setCellValue(wek.getEfficiencyboss());
				singleCell.setCellStyle(style);
				// 效果总评
				singleCell = singleRow.createCell(singleColIndex++);
				// 设置第一列的1-10行为下拉列表
				regions = new CellRangeAddressList(singleRowIndex - 1,
						singleRowIndex - 1, singleColIndex - 1,
						singleColIndex - 1);
				// 创建下拉列表数据
				constraint = DVConstraint
						.createExplicitListConstraint(strEffects);
				// 绑定
				dataValidation = new HSSFDataValidation(regions, constraint);
				singleSheet.addValidationData(dataValidation);
				singleCell.setCellValue(wek.getEffectboss());
				singleCell.setCellStyle(style);
				// 上级评分
				singleCell = singleRow.createCell(singleColIndex++);

				singleCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
				String bosspergsString = "IF(M" + (singleRowIndex) + "&N"
						+ (singleRowIndex) + "=\"提前完成超出预期\",130,IF(OR(M"
						+ (singleRowIndex) + "&N" + (singleRowIndex)
						+ "=\"按时完成超出预期\",M" + (singleRowIndex) + "&N"
						+ (singleRowIndex) + "=\"提前完成符合预期\"),110,IF(OR(M"
						+ (singleRowIndex) + "&N" + (singleRowIndex)
						+ "=\"按时完成符合预期\",M" + (singleRowIndex) + "&N"
						+ (singleRowIndex) + "=\"提前完成效果一般\"),100,IF(OR(M"
						+ (singleRowIndex) + "&N" + (singleRowIndex)
						+ "=\"滞后超出预期\",M" + (singleRowIndex) + "&N"
						+ (singleRowIndex) + "=\"按时完成效果一般\"),90,IF(M"
						+ (singleRowIndex) + "&N" + (singleRowIndex)
						+ "=\"滞后符合预期\",80,IF(M" + (singleRowIndex) + "&N"
						+ (singleRowIndex) + "=\"滞后效果一般\",50))))))*E"
						+ (singleRowIndex) + "+IF(OR(M" + (singleRowIndex)
						+ "=\"未启动\",N" + (singleRowIndex) + "=\"效果很差\"),0)*E"
						+ (singleRowIndex);
				singleCell.setCellFormula(bosspergsString);
				singleCell.setCellValue(wek.getBossper() == null ? 0 : wek
						.getBossper());
				singleCell.setCellStyle(style);
				// 上级评语
				singleCell = singleRow.createCell(singleColIndex++);
				singleCell.setCellValue(wek.getBossremark());
				singleCell.setCellStyle(style);

			} catch (Exception e) {
				e.printStackTrace();
				continue;
			}
		}
		singleRowIndex++;
		HSSFRow singleRow = singleSheet.createRow(singleRowIndex++);
		HSSFCell singleCell = singleRow.createCell(0);
		singleCell.setCellValue("自评分：");
		singleCell.setCellStyle(style);
		singleCell = singleRow.createCell(1);

		singleCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
		String selfpersumstr = "SUM(K8:K" + (singleRowIndex - 2) + ")";
		singleCell.setCellFormula(selfpersumstr);

		singleCell.setCellValue(selfperSum);
		singleCell.setCellStyle(style1);
		singleCell = singleRow.createCell(2);
		singleCell.setCellValue("上级评分：");
		singleCell.setCellStyle(style);
		singleCell = singleRow.createCell(3);

		singleCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
		String bossperstr = "SUM(O8:O" + (singleRowIndex - 2) + ")";
		singleCell.setCellFormula(bossperstr);

		singleCell.setCellValue(bossperSum);
		singleCell.setCellStyle(style1);
		singleSheet.setForceFormulaRecalculation(true);
	}

	@SuppressWarnings("unchecked")
	@Override
	public void exportDataAll(List<Map<String, Object>> listAllList,
			String templatePath, HttpServletResponse response) {
		// 获取文件输入流，创建工作簿
		FileInputStream fis;
		try {
			weekplanStatistics ww = (weekplanStatistics) listAllList.get(0)
					.get("huizong");
			int wkn = ww.getWeeknum();
			fis = new FileInputStream(new File(templatePath));
			HSSFWorkbook workBook = new HSSFWorkbook(fis);
			SimpleDateFormat myFmt = new SimpleDateFormat("yyyyMMddhhmm");
			String timeString = myFmt.format(new Date());
			// 设置导出文件名，并编码
			String fileName = ww.getDeptname() + "-周计划总结-" + wkn + "周.xls";
			response.reset();
			response.setContentType("application/octet-stream;charset=UTF-8");
			try {
				response.setHeader(
						"Content-Disposition",
						"attachment;Filename="
								+ new String(fileName.getBytes("GBK"),
										"ISO-8859-1"));
			} catch (Exception e) {
				e.printStackTrace();
				this.logger.error("导出统计报表信息出错！请联系管理员" + e.getMessage());
			}
			// 创建输出流
			OutputStream outputStream = new BufferedOutputStream(
					response.getOutputStream());
			for (int i = 0; i < listAllList.size(); i++) {
				Map<String, Object> map = (Map<String, Object>) listAllList
						.get(i);
				weekplanStatistics wStatistics = (weekplanStatistics) map
						.get("huizong");
				List<weekplan> list = (List<weekplan>) map.get("mingxi");
				exportData(workBook, list, wStatistics,
						wStatistics.getSelfpersum(),
						wStatistics.getBosspersum(), i + 1);
			}
			// 删除模板页
			workBook.removeSheetAt(0);
			// 将工作簿写入输出流
			workBook.write(outputStream);
			// 关闭资源流
			fis.close();
			outputStream.flush();
			outputStream.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.info(e.getMessage());
		}
	}

	@Override
	public List<Demand> selectDemand(String beginTime, String endTime,
			int isGuoQi, int statusFordemand) {
		// TODO Auto-generated method stub
		return this.weekPlanDao.selectDemand(beginTime, endTime, isGuoQi,
				statusFordemand);
	}

	@Override
	public int insertDemand(String beginTimeadd, String endTimeadd,
			String demandContent, int statusFordemandadd) {
		// TODO Auto-generated method stub
		return this.weekPlanDao.insertDemand(beginTimeadd, endTimeadd,
				demandContent, statusFordemandadd);
	}

	@Override
	public int editDemand(String beginTimeadd, String endTimeadd,
			String demandContent, int statusFordemandadd, int id) {
		// TODO Auto-generated method stub
		return this.weekPlanDao.editDemand(beginTimeadd, endTimeadd,
				demandContent, statusFordemandadd, id);
	}
}
